package com.rajeshDyadav.file.xml;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

public class InputExcel03 {
    public static void main(String[] args) throws IOException {
	String inputFilePath = "/home/base/test.xls";

	int columnRowNo = 3;

	InputExcel03 inputExcel = new InputExcel03();
	inputExcel.excelOperation(inputFilePath, columnRowNo);
    }

    public String excelOperation(String inputFilePath, int columnRowNo) {
	String status = "";
	try {
	    status = "Please Wait!!! This May Take Time..";

	    HSSFWorkbook currentWorkBook = new HSSFWorkbook(new FileInputStream(inputFilePath));
	    HSSFSheet currentSheet = currentWorkBook.getSheetAt(0);

	    HSSFRow currentRow;
	    HSSFCell currentCell;
	    int i;

	    currentRow = currentSheet.getRow(1);
	    currentCell = currentRow.getCell(5);
	    int noOfInstl = Double.valueOf(currentCell.getNumericCellValue()).intValue();

	    System.out.println("No. of Installments ..> " + noOfInstl);

	    ArrayList<java.util.Date> dueDateList = new ArrayList<java.util.Date>();
	    ArrayList<Double> principalList = new ArrayList<Double>();
	    ArrayList<Double> interestList = new ArrayList<Double>();
	    ArrayList<Double> emiAmtList = new ArrayList<Double>();

	    for (i = 0; i < (noOfInstl - 1); i++) {
		currentRow = currentSheet.getRow(8 + i);
		currentCell = currentRow.getCell(0);
		dueDateList.add(currentCell.getDateCellValue());
		currentCell = currentRow.getCell(1);
		principalList.add(currentCell.getNumericCellValue());
		currentCell = currentRow.getCell(2);
		interestList.add(currentCell.getNumericCellValue());
		currentCell = currentRow.getCell(3);
		emiAmtList.add(currentCell.getNumericCellValue());
	    }

	    Connection conn = null;
	    PreparedStatement pStmt = null;
	    String sql = "";

	    Class.forName("oracle.jdbc.driver.OracleDriver");
	    conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "fortune", "fortune");
	    conn.setAutoCommit(false);

	    for (i = 0; i < noOfInstl - 1; i++) {
		sql = "insert into f_appl_aprv_det(tran_id, line_no, principal, interest, emi_amt) values (?,?,?,?,?)";
		pStmt = conn.prepareStatement(sql);
		pStmt.setString(1, "TEST");
		pStmt.setInt(2, i + 1);
		pStmt.setDouble(3, principalList.get(i));
		pStmt.setDouble(4, interestList.get(i));
		pStmt.setDouble(5, emiAmtList.get(i));
		pStmt.executeUpdate();
		pStmt.close();
		pStmt = null;
		System.out.println("inserted record " + (i + 1));
	    }
	    System.out.println("all done");
	    conn.commit();
	    conn.close();
	} catch (Exception e) {
	    status = "!!! Error Occurred, May Be The Last Line !!!";
	    e.printStackTrace();
	}
	return status;
    }
}